All files / web/src/app/api/game-results/leaderboard/classroom/[classroomId] route.ts

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
/**
 * API route for classroom leaderboard
 *
 * GET /api/game-results/leaderboard/classroom/[classroomId] - Get rankings for all players in classroom
 */

import { NextResponse } from 'next/server'
import { db } from '@/db'
import { gameResults, classroomEnrollments, players } from '@/db/schema'
import { eq, desc, and, inArray, sql } from 'drizzle-orm'
import { withAuth } from '@/lib/auth/withAuth'
import { getUserId } from '@/lib/viewer'

/**
 * GET - Fetch classroom leaderboard
 *
 * Query params:
 * - gameName: Filter to specific game
 * - category: Filter to specific category
 */
export const GET = withAuth(async (request, { params }) => {
  try {
    const { classroomId } = (await params) as { classroomId: string }

    if (!classroomId) {
      return NextResponse.json({ error: 'Classroom ID required' }, { status: 400 })
    }

    // Authentication check (must be logged in)
    const userId = await getUserId()
    if (!userId) {
      return NextResponse.json({ error: 'Not authorized' }, { status: 403 })
    }

    const { searchParams } = new URL(request.url)
    const gameName = searchParams.get('gameName')
    const category = searchParams.get('category')

    // Get all players in this classroom
    const classmates = await db
      .select({ playerId: classroomEnrollments.playerId })
      .from(classroomEnrollments)
      .where(eq(classroomEnrollments.classroomId, classroomId))

    const playerIds = classmates.map((c) => c.playerId)

    // If no players in classroom, return empty rankings
    if (playerIds.length === 0) {
      return NextResponse.json({ rankings: [], playerCount: 0 })
    }

    // Build query conditions
    const conditions = [inArray(gameResults.playerId, playerIds)]
    if (gameName) conditions.push(eq(gameResults.gameName, gameName))
    if (category) conditions.push(eq(gameResults.category, category))

    // Get best scores per player
    const rankings = await db
      .select({
        playerId: gameResults.playerId,
        playerName: players.name,
        playerEmoji: players.emoji,
        bestScore: sql<number>`MAX(${gameResults.normalizedScore})`,
        gamesPlayed: sql<number>`COUNT(*)`,
        avgScore: sql<number>`AVG(${gameResults.normalizedScore})`,
        totalDuration: sql<number>`SUM(${gameResults.durationMs})`,
      })
      .from(gameResults)
      .innerJoin(players, eq(gameResults.playerId, players.id))
      .where(and(...conditions))
      .groupBy(gameResults.playerId, players.name, players.emoji)
      .orderBy(desc(sql`MAX(${gameResults.normalizedScore})`))

    // Add rank to each result
    const rankedResults = rankings.map((r, idx) => ({
      ...r,
      rank: idx + 1,
      avgScore: Math.round((r.avgScore ?? 0) * 10) / 10, // Round to 1 decimal
    }))

    // Get unique games played by these players (for filter dropdown)
    const gamesInClassroom = await db
      .select({
        gameName: gameResults.gameName,
        gameDisplayName: gameResults.gameDisplayName,
        gameIcon: gameResults.gameIcon,
      })
      .from(gameResults)
      .where(inArray(gameResults.playerId, playerIds))
      .groupBy(gameResults.gameName, gameResults.gameDisplayName, gameResults.gameIcon)

    return NextResponse.json({
      rankings: rankedResults,
      playerCount: playerIds.length,
      gamesAvailable: gamesInClassroom,
    })
  } catch (error) {
    console.error('Error fetching classroom leaderboard:', error)
    return NextResponse.json({ error: 'Failed to fetch leaderboard' }, { status: 500 })
  }
})